// createTables.js
const connection = require('./database');

const createUsersTable = `
CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(255) NOT NULL UNIQUE,
  password VARCHAR(255) NOT NULL,
  role ENUM('0', '1') NOT NULL DEFAULT '1'
)`;

const createWordsTable = `
CREATE TABLE IF NOT EXISTS words (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  description TEXT,
  samewordlist JSON DEFAULT NULL
)`;

const createWordsetsTable = `
CREATE TABLE IF NOT EXISTS wordsets (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  img LONGTEXT,
  wordlist JSON DEFAULT NULL,
  user_id INT NOT NULL,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
)`;

const createArticlesTable = `
CREATE TABLE IF NOT EXISTS articles (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  content LONGTEXT NOT NULL,
  img LONGTEXT,
  status ENUM('pending', 'approved', 'rejected') NOT NULL DEFAULT 'pending',
  username VARCHAR(255),
  user_id INT NOT NULL,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
)`;

const createTestsTable = `
CREATE TABLE IF NOT EXISTS tests (
  id INT AUTO_INCREMENT PRIMARY KEY,
  wordset_id INT NOT NULL,
  user_id INT NOT NULL,
  score INT DEFAULT 0,
  completed BOOLEAN NOT NULL DEFAULT FALSE,
  test_data JSON DEFAULT NULL,
  FOREIGN KEY (wordset_id) REFERENCES wordsets(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
)`;

const createUserWordsetsTable = `
CREATE TABLE IF NOT EXISTS user_wordsets (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  wordset_id INT NOT NULL,
  UNIQUE INDEX user_wordset_unique (user_id, wordset_id),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (wordset_id) REFERENCES wordsets(id) ON DELETE CASCADE
)`;

const createTables = async () => {
  await connection.promise().query(createUsersTable);
  await connection.promise().query(createWordsTable);
  await connection.promise().query(createWordsetsTable);
  await connection.promise().query(createArticlesTable);
  await connection.promise().query(createTestsTable);
  await connection.promise().query(createUserWordsetsTable);
  console.log('All tables created.');
};

createTables().then(() => {
  connection.end();
});